{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# 1. 导入库\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from matplotlib import rcParams\n",
    "rcParams['font.sans-serif'] = ['SimHei'] #win\n",
    "rcParams['font.sans-serif'] = ['STHeiti'] #mac"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 2. 导入数据\n",
    "df = pd.read_csv('data/house_sales.csv')"
   ],
   "id": "394fe6d40298aa8a",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 3. 数据概览\n",
    "print('总记录数：', len(df))\n",
    "print('字段数量：', len(df.columns))\n",
    "df.head(5)\n",
    "df.info()"
   ],
   "id": "2766bb54f599549e",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 4. 数据清洗\n",
    "# 删除无用的数据列\n",
    "df.drop(columns='origin_url',inplace=True)"
   ],
   "id": "d4fbe899ca9cbac1",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 检查是否有缺失值\n",
    "df.isna().sum()\n",
    "# 删除缺失值\n",
    "df.dropna(inplace=True)"
   ],
   "id": "37338d9fda8ee61f",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 检查是否有重复值\n",
    "df.duplicated().sum()\n",
    "# 删除重复数据\n",
    "df.drop_duplicates(inplace=True)\n",
    "# print(len(df))\n",
    "# 面积的数据类型转换\n",
    "df['area'] = df['area'].str.replace('㎡','').astype(float)\n",
    "# 售价的数据类型转换\n",
    "df['price'] = df['price'].str.replace('万','').astype(float)\n",
    "# 朝向的数据类型转换\n",
    "df['toward'] = df['toward'].astype('category')\n",
    "# 单价的数据类型转换\n",
    "df['unit'] = df['unit'].str.replace('元/㎡','').astype(float)\n",
    "# 建造年份的数据类型转换\n",
    "df['year'] = df['year'].str.replace('年建','').astype(int)"
   ],
   "id": "6f8b488cd6fcae49",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 异常值的处理\n",
    "# 房屋面积的异常处理\n",
    "df = df[ (df['area']<600) & (df['area']>20)]"
   ],
   "id": "7a7eda8d461873fa",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 房屋售价的异常处理  IQR\n",
    "Q1 = df['price'].quantile(0.25)\n",
    "Q3 = df['price'].quantile(0.75)\n",
    "IQR = Q3 - Q1\n",
    "low_price = Q1 - 1.5*IQR\n",
    "high_price = Q3 + 1.5*IQR\n",
    "df = df[ (df['price']<high_price) & (df['price']>low_price) ]"
   ],
   "id": "f610771d815af0f8",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 5. 新数据特征构造\n",
    "# 地区district\n",
    "df['district'] = df['address'].str.split('-').str[0]\n",
    "# 楼层的类型floor_type\n",
    "df['floor_type'] = df['floor'].str.split('（').str[0].astype('category')\n",
    "def fun1(str1):\n",
    "    if pd.isna(str1):\n",
    "        return '未知'\n",
    "    elif '低' in str1:\n",
    "        return '低楼层'\n",
    "    elif '中' in str1:\n",
    "        return '中楼层'\n",
    "    elif '高' in str1:\n",
    "        return '高楼层'\n",
    "    else:\n",
    "        return '未知'\n",
    "df['floor_type2'] = df['floor'].apply(fun1).astype('category')\n",
    "# 是否是直辖市zxs\n",
    "df['zxs'] = df['city'].apply(lambda x: 1 if x in ['北京','上海','天津','重庆'] else 0)\n",
    "# 卧室的数量bedrooms\n",
    "df['bedrooms'] = df['rooms'].str.split('室').str[0].astype(int)\n",
    "# 客厅的数量livingrooms\n",
    "# df['rooms'].str.split('室').str[1].str.split('厅').str[0].astype(int)\n",
    "df['livingrooms'] = df['rooms'].str.extract(r'(\\d+)厅').astype('int')\n",
    "# 楼龄building_age\n",
    "df['building_age'] = 2025 - df['year']\n",
    "# 价格的分段price_labels\n",
    "df['price_labels'] = pd.cut(df['price'],bins=4,labels=['低价','中价','高价','豪华'])"
   ],
   "id": "103d9d826cfdfb6b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "# 6. 问题分析及可视化",
   "id": "196e377f3a092d8f",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A1\n",
    "问题: 哪些变量最影响房价？面积、楼层、房间数哪个影响更大？\n",
    "分析主题: 特征相关性\n",
    "分析目标: 了解房屋各特征对房价的线性影响\n",
    "分组字段: 无\n",
    "指标/方法: 皮尔逊相关系数\n",
    "'''\n",
    "# 选择数值型特征\n",
    "a = df[['price','area','unit','building_age']].corr()#相关系数\n",
    "# 对房价的影响最大的几个因素的排序\n",
    "a['price'].sort_values(ascending=False)[1:]\n",
    "# 相关性的热力图\n",
    "plt.figure(figsize = (5,5))\n",
    "sns.heatmap(a,cmap='coolwarm')\n",
    "plt.title('房屋特征相关性热力图')\n",
    "plt.tight_layout()\n",
    "# df.head()"
   ],
   "id": "b3272a6fea507fc",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A2\n",
    "问题: 全国房价总体分布是怎样的？是否存在极端值？\n",
    "分析主题: 描述性统计\n",
    "分析目标: 概览数值型字段的分布特征\n",
    "分组字段: 无\n",
    "指标/方法: 平均数/中位数/四分位数/标准差\n",
    "'''\n",
    "df.describe()\n",
    "# 房价分布直方图\n",
    "plt.subplot(111)\n",
    "plt.hist(df['price'],bins=10)\n",
    "df.head()\n",
    "sns.histplot(data=df,x='price',bins=10,kde=True)"
   ],
   "id": "d8339ee6e6b6938b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A3\n",
    "问题: 哪些城市房价最高？直辖市与非直辖市差异如何？\n",
    "分析主题: 城市对比\n",
    "分析目标: 比较不同城市房价水平\n",
    "分组字段: city\n",
    "指标/方法: 均价/单价中位数/箱线图\n",
    "'''\n",
    "# 按城市统计\n",
    "city_stats = df.groupby('city').agg({\n",
    "    'price': ['mean', 'median', 'count'],\n",
    "    'unit': ['mean', 'median']\n",
    "})\n",
    "print(\"\\n各城市房价统计:\")\n",
    "display(city_stats.sort_values(('unit', 'mean'), ascending=False).head(10))\n",
    "\n",
    "# 可视化前10城市\n",
    "top_cities = city_stats.sort_values(('unit', 'mean'), ascending=False).head(10).index\n",
    "df_top = df[df['city'].isin(top_cities)]\n",
    "\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.boxplot(x='city', y='price', data=df_top, order=top_cities)\n",
    "plt.title('TOP10城市房价分布对比', fontsize=14)\n",
    "plt.xlabel('城市')\n",
    "plt.ylabel('价格(元)')\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ],
   "id": "87a1cf3bb5b73bb7",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A4\n",
    "问题: 高价房在面积、楼层等方面有什么特征？\n",
    "分析主题: 价格分层\n",
    "分析目标: 识别不同价位房屋特征差异\n",
    "分组字段: 价格分段(低中高)\n",
    "指标/方法: 列联表/卡方检验\n",
    "'''\n",
    "\"\"\"A4 价格分层特征差异分析\"\"\"\n",
    "print(\"\\n=== A4 价格分层特征差异 ===\")\n",
    "\n",
    "# 按价格分段分析特征\n",
    "price_group = df.groupby('price_labels').agg({\n",
    "    'area': ['mean', 'median'],\n",
    "    'building_age': 'mean',\n",
    "    'unit': 'median',\n",
    "    'zxs': 'mean'  # 直辖市占比\n",
    "})\n",
    "\n",
    "print(\"\\n各价格层级特征对比:\")\n",
    "display(price_group)\n",
    "\n",
    "# 可视化\n",
    "plt.figure(figsize=(14, 5))\n",
    "\n",
    "plt.subplot(131)\n",
    "sns.barplot(x='price_labels', y='area', data=df, estimator=np.median)\n",
    "plt.title('不同价格层级面积对比')\n",
    "plt.ylabel('面积(㎡)')\n",
    "\n",
    "plt.subplot(132)\n",
    "sns.boxplot(x='price_labels', y='building_age', data=df)\n",
    "plt.title('不同价格层级楼龄分布')\n",
    "plt.ylabel('楼龄(年)')\n",
    "\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ],
   "id": "913e7f3d09685bea",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A5\n",
    "问题: 哪种户型最受欢迎？三室比两室贵多少？\n",
    "分析主题: 户型分析\n",
    "分析目标: 分析不同户型的市场表现\n",
    "分组字段: rooms\n",
    "指标/方法: 占比/平均单价/溢价率\n",
    "'''\n",
    "\"\"\"A5 户型市场表现分析\"\"\"\n",
    "print(\"\\n=== A5 户型分析 ===\")\n",
    "\n",
    "# 提取房间数（示例：\"3室2厅\" -> 3）\n",
    "df['room_count'] = df['rooms'].str.extract('(\\d+)室').astype(float)\n",
    "\n",
    "# 按户型统计\n",
    "room_stats = df.groupby('room_count').agg({\n",
    "    'price': ['mean', 'median'],\n",
    "    'unit': 'median',\n",
    "    'area': 'median',\n",
    "    'city': 'nunique'\n",
    "}).sort_values(('price', 'mean'))\n",
    "\n",
    "print(\"\\n各户型市场表现:\")\n",
    "display(room_stats)\n",
    "\n",
    "# 可视化\n",
    "plt.figure(figsize=(14, 5))\n",
    "\n",
    "plt.subplot(131)\n",
    "sns.boxplot(x='room_count', y='price', data=df)\n",
    "plt.title('不同户型总价分布')\n",
    "plt.xlabel('房间数')\n",
    "\n",
    "plt.subplot(132)\n",
    "sns.scatterplot(x='area', y='price', hue='room_count', data=df, palette='viridis')\n",
    "plt.title('面积-价格-户型关系')\n",
    "\n",
    "plt.subplot(133)\n",
    "sns.barplot(x='room_count', y='unit', data=df, estimator=np.median)\n",
    "plt.title('不同户型单价对比')\n",
    "plt.xlabel('房间数')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ],
   "id": "4a72a40ab40c5623",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "'''\n",
    "问题编号: A6\n",
    "问题: 南北向是否真比单一朝向贵？贵多少？\n",
    "分析主题: 朝向溢价\n",
    "分析目标: 评估不同朝向的价格差异\n",
    "分组字段: toward\n",
    "指标/方法: 方差分析/多重比较\n",
    "'''\n",
    "df['toward'].value_counts()\n",
    "df.groupby('toward').agg({\n",
    "    'price':['mean','median'],\n",
    "    'unit':'median',\n",
    "    'building_age':'mean',\n",
    "})\n",
    "# 数据可视化\n",
    "plt.figure(figsize=(14, 5))\n",
    "sns.boxplot(x='toward', y='price', data=df)\n",
    "plt.tight_layout()"
   ],
   "id": "b6b6ff2e082670de",
   "outputs": [],
   "execution_count": null
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
